﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Shutterstock.Salesforce.SSISDownload.SFTableOperations
{
    /// <summary>
    /// Represents an Indexes Replicator object
    /// </summary>
    public static class IndexesReplicator
    {
        /// <summary>
        /// Replicate the Indexes of the given table.
        /// </summary>
        /// <param name="conn">SQL Connection.</param>
        /// <param name="indexesSource">The source table Name of the indexes replicated from.</param>
        /// <param name="indexesDestination">The destination table name of the indexes replicated to.</param>
        public static void Replicate(SqlConnection conn, string indexesSource, string indexesDestination)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            // Get the indexes information from the indexes source.
            SqlCommand cmd = conn.CreateCommand();
            StringBuilder query = new StringBuilder();
            query.AppendLine("select si.*");
            query.AppendLine("	, sic.index_column_id");
            query.AppendLine("	, sic.key_ordinal");
            query.AppendLine("	, sic.partition_ordinal");
            query.AppendLine("	, isnull(sic.is_descending_key,0) is_descending_key");
            query.AppendLine("	, isnull(sic.is_included_column,0) is_included_column");
            query.AppendLine("	, sc.name column_name");
            query.AppendLine("	, sds.name data_space_name");
            query.AppendLine("   , isnull(compression,0) compression");
            query.AppendLine(" from sys.objects so");
            query.AppendLine("  , sys.indexes si LEFT OUTER JOIN ");
            query.AppendLine("                   ( select object_id, index_id, MAX(data_compression) compression ");
            query.AppendLine("                     FROM sys.partitions where object_id = object_id('").Append(indexesSource).Append("') group by object_id,index_id ");
            query.AppendLine("                    ) Comp on Comp.object_id= SI.object_id and COMP.index_id = SI.index_id");
            query.AppendLine("	, sys.index_columns sic");
            query.AppendLine("	, sys.columns sc");
            query.AppendLine("	, sys.data_spaces sds");
            query.AppendLine("where so.name = '").Append(indexesSource ).Append("'");
            query.AppendLine("	and so.object_id = si.object_id");
            query.AppendLine("	and si.object_id = sic.object_id ");
            query.AppendLine("	and si.index_id = sic.index_id");
            query.AppendLine("	and sic.object_id = sc.object_id ");
            query.AppendLine("	and sc.column_id = sic.column_id ");
            query.AppendLine("	and si.data_space_id = sds.data_space_id ");
            query.AppendLine("  order by 3,sic.index_column_id");

            cmd.CommandText = query.ToString();
            SqlDataReader dr = cmd.ExecuteReader();

            // create the indexes
            Dictionary<int, Index> indexes = new Dictionary<int, Index>();
            int index_id, fill_factor, key_ordinal;
            bool ignore_dup_key, is_padded, allow_row_locks, allow_page_locks, is_included_column, is_descending_key;
            DataCompression compressionOption;
            Index index;
            string column_name;
            while (dr.Read())
            {
                index_id = int.Parse(dr["index_id"].ToString());
                if (!indexes.ContainsKey(index_id))
                {
                    index = new Index();
                    indexes.Add(index_id, index);
                    index.IndexName = dr["name"].ToString();
                    index.TableName = indexesDestination;
                    index.IsUnique = (bool)dr["is_unique"];
                    index.Clustered = dr["type_desc"].ToString();
                    index.DataSpaceName = dr["data_space_name"].ToString();
                    ignore_dup_key = (bool)dr["ignore_dup_key"];
                    fill_factor = int.Parse(dr["fill_factor"].ToString());
                    is_padded = (bool)dr["is_padded"];
                    allow_page_locks = (bool)dr["allow_page_locks"];
                    allow_row_locks = (bool)dr["allow_row_locks"];
                    compressionOption = (DataCompression)int.Parse(dr["compression"].ToString());
                    index.SetRelationalIndexOptions(ignore_dup_key, fill_factor, is_padded, allow_row_locks, allow_page_locks, compressionOption);
                }
                else
                {
                    index = indexes[index_id];
                }
                column_name = dr["column_name"].ToString();
                if (dr["partition_ordinal"].ToString() != "0")
                {
                    index.PartitionColumn = column_name;
                }
                key_ordinal = int.Parse(dr["key_ordinal"].ToString());
                is_descending_key = (bool)dr["is_descending_key"];
                is_included_column = (bool)dr["is_included_column"];
                if (key_ordinal != 0)
                {
                    index.AddColumn(key_ordinal, column_name, is_descending_key);
                }
                if (is_included_column)
                {
                    index.AddIncludedColumn(column_name);
                }
            }
            dr.Close();

            //get queries to create indexes in the the destination table/view
            foreach (Index ind in indexes.Values)
            {
                cmd.CommandText = ind.CreateIndexQuery();
                cmd.CommandTimeout = 300;
                cmd.ExecuteNonQuery();
            }
        }
    }
}
